/*
 * Copyright (C) 2021 Nain57
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; If not, see <http://www.gnu.org/licenses/>.
 */
package com.buzbuz.smartautoclicker.database.room.migrations

import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

/**
 * Migration from database v3 to v4.
 *
 * Complete refactoring of the database. Clicks are now Events, and they contains several Actions. This required to
 * change a lot the database. All those sqlite request members are extracted from the schema generated by room.
 *
 * Changes:
 * * click_table is now event_table
 * * conditions no longer have several events/clicks
 * * creation of an action table.
 */
object Migration3to4 : Migration(3, 4) {

    override fun migrate(database: SupportSQLiteDatabase) {
        database.apply {
            // Creates the new tables.
            execSQL(createEventTable)
            execSQL(createActionTable)
            execSQL(createConditionTable)

            // Create the indexes fot the relations
            execSQL(scenarioToEventsIndex)
            execSQL(eventToActionsIndex)
            execSQL(eventToConditionsIndex)

            // Transform the clicks into event
            execSQL(insertEvents)

            // Update condition table
            execSQL(insertConditions)

            // Transform the clicks into actions.
            execSQL(insertClickActions)
            execSQL(insertSwipeActions)
            execSQL(insertPauseActions)

            // Delete old tables
            execSQL(deleteClickTable)
            execSQL(deleteConditionTable)
            execSQL(deleteClickConditionCrossRefTable)

            // Delete old indexes
            execSQL(deleteScenarioClickIndex)
            execSQL(deleteClickConditionCrossRefClickIndex)
            execSQL(deleteClickConditionCrossRefConditionIndex)

            // Rename condition_table_new
            execSQL(renameNewConditionTable)
        }
    }

    /** Create the event table, replacement of the click table. */
    private val createEventTable = """
        CREATE TABLE IF NOT EXISTS `event_table` (
            `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            `scenario_id` INTEGER NOT NULL,
            `name` TEXT NOT NULL,
            `operator` INTEGER NOT NULL,
            `priority` INTEGER NOT NULL,
            `stop_after` INTEGER,
            FOREIGN KEY(`scenario_id`) REFERENCES `scenario_table`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE 
        )
    """.trimIndent()

    /** Create the new action table. */
    private val createActionTable = """
        CREATE TABLE IF NOT EXISTS `action_table` (
            `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            `eventId` INTEGER NOT NULL,
            `priority` INTEGER NOT NULL,
            `name` TEXT NOT NULL DEFAULT "Default name",
            `type` TEXT NOT NULL,
            `x` INTEGER,
            `y` INTEGER,
            `pressDuration` INTEGER,
            `fromX` INTEGER,
            `fromY` INTEGER,
            `toX` INTEGER,
            `toY` INTEGER,
            `swipeDuration` INTEGER,
            `pauseDuration` INTEGER,
            FOREIGN KEY(`eventId`) REFERENCES `event_table`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE
        )
    """.trimIndent()

    /**
     * Creates the condition table. As the changes on the old condition table are too big, we create a new one here
     * to copy all the values from the old one before renaming this one.
     */
    private val createConditionTable = """
        CREATE TABLE IF NOT EXISTS `condition_table_new` (
            `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            `eventId` INTEGER NOT NULL,
            `path` TEXT NOT NULL,
            `area_left` INTEGER NOT NULL,
            `area_top` INTEGER NOT NULL,
            `area_right` INTEGER NOT NULL,
            `area_bottom` INTEGER NOT NULL,
            `threshold` INTEGER NOT NULL DEFAULT 1,
            FOREIGN KEY(`eventId`) REFERENCES `event_table`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE
        )
    """.trimIndent()

    /** Creates the index between a scenario and its events. */
    private val scenarioToEventsIndex = """
        CREATE INDEX IF NOT EXISTS `index_event_table_scenario_id` ON `event_table` (`scenario_id`)
    """.trimIndent()

    /** Creates the index between an event and its actions. */
    private val eventToActionsIndex = """
        CREATE INDEX IF NOT EXISTS `index_action_table_eventId` ON `action_table` (`eventId`)
    """.trimIndent()

    /** Creates the index between an event and its conditions. */
    private val eventToConditionsIndex = """
        CREATE INDEX IF NOT EXISTS `index_condition_table_eventId` ON `condition_table_new` (`eventId`)
    """.trimIndent()

    /** Insert an event for each old click. */
    private val insertEvents = """
        INSERT INTO `event_table` (id, scenario_id, name, operator, priority, stop_after)
        SELECT clickId, scenario_id, name, operator, priority, stop_after
        FROM click_table
    """.trimIndent()

    /** Insert an action for each old click. */
    private val insertClickActions = """
        INSERT INTO action_table (eventId, priority, name, type, x, y, pressDuration)
        SELECT clickId, 0, name, "CLICK", from_x, from_y, 1
        FROM click_table
        WHERE click_table.type = 1
    """.trimIndent()

    /** Insert an action for each old swipe. */
    private val insertSwipeActions = """
        INSERT INTO action_table (eventId, priority, name, type, fromX, fromY, toX, toY, swipeDuration)
        SELECT clickId, 0, name, "SWIPE", from_x, from_y, to_x, to_y, 175
        FROM click_table
        WHERE click_table.type = 2
    """.trimIndent()

    /** Insert a pause action for each old action we had to replace the delay_after. */
    private val insertPauseActions = """
        INSERT INTO action_table (eventId, priority, name, type, pauseDuration)
        SELECT clickId, 1, "Pause", "PAUSE", delay_after
        FROM click_table
    """.trimIndent()

    /** Copy the existing conditions into the new table. */
    private val insertConditions = """
        INSERT INTO `condition_table_new` (eventId, path, area_left, area_top, area_right, area_bottom, threshold)
        SELECT ClickConditionCrossRef.clickId, ClickConditionCrossRef.path, area_left, area_top, area_right, area_bottom, threshold 
        FROM condition_table
        INNER JOIN ClickConditionCrossRef on ClickConditionCrossRef.path = condition_table.path
    """.trimIndent()

    /** Delete the old index between a scenario and its clicks. */
    private val deleteScenarioClickIndex = """
        DROP INDEX IF EXISTS index_click_table_scenario_id
    """.trimIndent()

    /** Delete the old click table. */
    private val deleteClickTable = """
        DROP TABLE IF EXISTS click_table
    """.trimIndent()

    /** Delete the old index between a click and its conditions. */
    private val deleteClickConditionCrossRefClickIndex = """
        DROP INDEX IF EXISTS index_ClickConditionCrossRef_clickId
    """.trimIndent()

    /** Delete the old index between a condition and its clicks. */
    private val deleteClickConditionCrossRefConditionIndex = """
        DROP INDEX IF EXISTS index_ClickConditionCrossRef_path
    """.trimIndent()

    /** Delete the old condition table. */
    private val deleteConditionTable = """
        DROP TABLE condition_table
    """.trimIndent()

    /** Delete the old click condition cross ref table. */
    private val deleteClickConditionCrossRefTable = """
        DROP TABLE ClickConditionCrossRef
    """.trimIndent()

    /** Rename the new condition table with the old name. */
    private val renameNewConditionTable = """
        ALTER TABLE condition_table_new RENAME TO condition_table
    """.trimIndent()
}